package DianShang_2024.ds_01.indicator

import org.apache.spark.sql.{DataFrame, SparkSession}

import java.util.Properties

object test {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .master("local[*]")
      .appName("指标计算第四题")
      .enableHiveSupport()
      .getOrCreate()

    spark.sql("use dwd")

    //  准备连接mysql需要用到的配置
    val jdbc_conf = new Properties()
    jdbc_conf.setProperty("user", "root")
    jdbc_conf.setProperty("password", "123456")
    jdbc_conf.setProperty("driver", "com.mysql.jdbc.Driver")

    //  在select后面的+号表示字段的值进行相加的操作

    val data: DataFrame = spark.sql(
      """
        |select
        |t1.user_id as userid,
        |t2.name as username,
        |date_format(t1.create_time,"yyyyMMdd") as day,
        |sum(t1.final_total_amount) as today_price
        |
        |from
        |dwd.fact_order_info t1
        |join dim_user_info t2 on t1.user_id=t2.id and t1.etl_date='20231017' and t2.etl_date='20231017'
        |group by t1.user_id,t2.name,day
        |""".stripMargin)

data.createOrReplaceTempView("aa")
    spark.sql("select  * from aa where username='李斌'").show


    //  关闭sparksql的环境
    spark.close()
  }

}
